﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Windows.Forms;

namespace RN_DirectService_init
{
    public partial class FrmSPT : Form
    {
        // 总部主数据库连接字符串
        private readonly string mainDB = "Data Source=127.0.0.1;Initial Catalog=" + "RN_test" + ";User ID=" + "sa" + ";Password=" + "rn-123456" + ";";

        // 总部邮件数据库连接字符串
        private readonly string emailDB = "Data Source=127.0.0.1;Initial Catalog=" + "rn_test_email" + ";User ID=" + "sa" + ";Password=" + "rn-123456" + ";";

        // 客户地区 与 工厂分部 对照表
        private Dictionary<string, string> map = null;

        // 客户信息类
        private class u_cust
        {
            // 客户ID
            public int cusid { get; set; }

            // 客户的企业代号
            public string commcode { get; set; }

            // 客户地区
            public string areaname { get; set; }

            // 构造方法
            public u_cust(int cusid, string commcode, string areaname)
            {
                this.cusid = cusid;
                this.commcode = commcode;
                this.areaname = areaname;
            }
        }

        public FrmSPT()
        {
            InitializeComponent();
        }

        // 加载事件(尝试连接总部主、邮件数据库，初始化)
        private void FrmSPT_Load(object sender, EventArgs e)
        {
            Trace.Write("----------更新客户信息开始----------");
            try
            {
                using (var connMain = new SqlConnection(mainDB))
                using (var connEmail = new SqlConnection(emailDB))
                {
                    connMain.Open();
                    connEmail.Open();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                Trace.Write(ex.ToString());
                return;
            }
            
            InitMap();

            MessageBox.Show("连接成功");
        }

        // 初始化 客户地区 与 工厂分部 对照表
        private void InitMap()
        {
            map = new Dictionary<string, string>();
            map.Add("艾驰", "艾驰");
            map.Add("槟城", "槟城印象");
            map.Add("吉森", "吉森");
            map.Add("软体", "沙发");
            map.Add("锐奇", "锐奇");
            map.Add("新宠", "艾驰(新)");
            map.Add("优米诺", "优米诺");
        }

        // 【按钮】执行
        private void btnGo_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("确定要执行？", "", MessageBoxButtons.YesNoCancel) != DialogResult.Yes)
            {
                return;
            }

            try
            {
                UpdateCustInfo();
                UpdateCustCommsccode();
            }
            catch (Exception ex)
            {
                Trace.Write(ex.ToString());
            }

            MessageBox.Show("执行完成，请查看日志文件log.txt");
        }

        // 更新使用电子商务的客户的信息
        private void UpdateCustInfo()
        {
            List<u_cust> custList = null;

            using (var connMain = new SqlConnection(mainDB))
            using (var cmdMain = connMain.CreateCommand())
            using (var connEmail = new SqlConnection(emailDB))
            using (var cmdEmail = connEmail.CreateCommand())
            {
                connMain.Open();
                connEmail.Open();

                // 选出使用电子商务的客户
                cmdMain.CommandText = "select cusid, commcode, areaname    from u_cust    where ifecomm = 1 and commcode <> ''";
                using (var reader = cmdMain.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        custList = new List<u_cust>();
                        while (reader.Read())
                        {
                            int cusid = Convert.ToInt32(reader["cusid"]);
                            string commcode = reader["commcode"].ToString().Trim();
                            string areaname = reader["areaname"].ToString().Trim();
                            custList.Add(new u_cust(cusid, commcode, areaname));
                        }
                    }
                }

                if (custList == null || custList.Count == 0)
                {
                    MessageBox.Show("没有客户使用电子商务(ifecomm = 1 and commcode <> '')");
                    return;
                }

                int cusCommID, myRelID;
                foreach (var cust in custList)
                {
                    // 根据客户的企业代号 查找 CusCommID
                    cmdEmail.CommandText = "select top 1 CusCommID    from LJFIEB_CusComm    where CusCode = @commcode";
                    cmdEmail.Parameters.Clear();
                    cmdEmail.Parameters.AddWithValue("@commcode", cust.commcode);

                    using (var reader = cmdEmail.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            cusCommID = Convert.ToInt32(reader["CusCommID"]);
                        }
                        else
                        {
                            Trace.Write(string.Format("失败！！客户[{0},{1},{2}]找不到LJFIEB_CusComm.CusCommID", cust.cusid, cust.commcode, cust.areaname));
                            continue;
                        }
                    }

                    foreach (var item in map)
                    {
                        if (cust.areaname.Contains(item.Key))
                        {
                            cmdEmail.CommandText = "select top 1 MyRelID    from LJFIEB_CusComm_RelMx    where MyRelType = 1 and CusCommID = @cusCommID and MyRelName = @relname";
                            cmdEmail.Parameters.Clear();
                            cmdEmail.Parameters.AddWithValue("@cusCommID", cusCommID);
                            cmdEmail.Parameters.AddWithValue("@relname", item.Value);

                            using (var reader = cmdEmail.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    myRelID = Convert.ToInt32(reader["MyRelID"]);
                                }
                                else
                                {
                                    Trace.Write(string.Format("失败！！客户[{0},{1},{2}]找不到LJFIEB_CusComm_RelMx.MyRelID,找不到关联[{3}]", cust.cusid, cust.commcode, cust.areaname, item.Value));
                                    break;
                                }
                            }

                            cmdMain.CommandText = "update u_cust    set commMyRelID = @myRelID    where cusid = @cusid";
                            cmdMain.Parameters.Clear();
                            cmdMain.Parameters.AddWithValue("@myRelID", myRelID);
                            cmdMain.Parameters.AddWithValue("@cusid", cust.cusid);

                            cmdMain.ExecuteNonQuery();

                            break;
                        }
                    }
                }
            }
        }

        // 将所有使用电子商务 并 绑定身份 的 客户，绑定分部代号
        private void UpdateCustCommsccode()
        {
            using (var conn = new SqlConnection(mainDB))
            using (var cmd = conn.CreateCommand())
            {
                conn.Open();

                cmd.CommandText = @"
update u_cust set commsccode = 
	case when areaname like '艾驰%' then '02'
		 when areaname like '槟城%' then '03'
		 when areaname like '吉森%' then '02'
		 when areaname like '软体%' then '06'
		 when areaname like '锐奇%' then '03'
		 when areaname like '新宠%' then '05'
		 when areaname like '优米诺%' then '05'
	end
where ifecomm = 1 and commMyRelID > 0
";
                cmd.ExecuteNonQuery();
            }
        }
    }
}
